package cmpe273.classproject.server.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import cmpe273.classproject.server.model.Movie;
import cmpe273.classproject.server.model.Person;
import cmpe273.classproject.server.model.RentalTransaction;

public class VideoRentalDao {

	private Cache cache;

	public VideoRentalDao() {
		cache = Cache.getInstance();
	}
	
	private Connection getConnection() throws SQLException {
		DataSource dataSource = null;
		try {
			Context initContext = new InitialContext();
			Context envContext = (Context) initContext.lookup("java:/comp/env");
			dataSource = (DataSource) envContext.lookup("jdbc/testdb");
		} catch (NamingException e) {
			//Nothing we can do about it...
			e.printStackTrace();
		}
		return dataSource.getConnection();
	}

	private Connection getConnection2() throws SQLException {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			throw new RuntimeException("Couldn't load databasedriver...");
		}
		Connection conn = null;
		conn = DriverManager.getConnection("jdbc:mysql://localhost/dvideolibrary", "root", "root");
		return conn;
	}


	public void submitMovie(RentalTransaction tx) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		PreparedStatement ps3 = null;
		ResultSet resultSet = null;
		ResultSet rentalIdResult = null;
		
		PreparedStatement rentalIdPs = null;
		
		String rentalIdString = "select rentalID from tblrenttransaction where memberID = ? and movieID = ? and outstanding = 1";
		String sqlString = "select availcopies from tblmovie where movieID = ?";
		String sqlString2 = "update tblrenttransaction set outstanding = ? where rentalID = ?";
		String sqlString3 = "update tblmovie set availcopies = ? where movieID = ?";

		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			
			//find the rental id
			rentalIdPs = conn.prepareStatement(rentalIdString);
			rentalIdPs.setInt(1, tx.getMemberId());
			rentalIdPs.setInt(2, tx.getMovieId());
			rentalIdResult = rentalIdPs.executeQuery();
			rentalIdResult.next();
			int rentalID = rentalIdResult.getInt("rentalID");
			
			ps = conn.prepareStatement(sqlString);
			ps.setInt(1, tx.getMovieId());
			resultSet = ps.executeQuery();
			resultSet.next();
			int availCopies = resultSet.getInt("availcopies");
			ps2 = conn.prepareStatement(sqlString2);
			ps2.setBoolean(1, false);
			ps2.setInt(2, rentalID);
			ps2.executeUpdate();

			ps3 = conn.prepareStatement(sqlString3);
			ps3.setInt(1, tx.getMovieId());
			ps3.setInt(2, availCopies + 1);
			ps3.executeUpdate();
			conn.commit();
		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(ps2);
			DaoHelper.close(ps3);
			DaoHelper.close(resultSet);
			DaoHelper.close(conn);
		}
	}

	public int createMovie(Movie movie) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		int result;
		String selectString = "select moviename from tblmovie where moviename = ?";
		String insertString = "insert into tblmovie(moviename,mbanner,releasedate,rentamount,category,totalcopies, availcopies)"
				+ " values (?, ?, ?, ?, ?, ?, ?)";
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(selectString);
			ps.setString(1, movie.getMovieName());
			resultSet = ps.executeQuery();
			// If movie exists already
			if (resultSet.next()) {
				result = DaoConst.DUPLICATE;
			} else {
				ps = conn.prepareStatement(insertString);
				ps.setString(1, movie.getMovieName());
				ps.setString(2, movie.getMovieBanner());
				ps.setDate(3, new java.sql.Date(movie.getReleaseDate()
						.getTime()));
				ps.setInt(4, movie.getRentAmount());
				ps.setString(5, movie.getMovieCategory());
				ps.setInt(6, movie.getTotalCopies());
				ps.setInt(7, movie.getTotalCopies()); // initial avail = total
				result = ps.executeUpdate();
			}
			conn.commit();
			conn.setAutoCommit(true);
			return result;
		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(resultSet);
			DaoHelper.close(conn);
		}
	}

	public Movie readMovie(int movieId) throws SQLException {
		Movie movie = null;//(Movie) cache.get(movieId);
//		if (movie == null) {
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet resultSet = null;
			String selectString = "select * from tblmovie where movieID = ?";
			try {
				conn = getConnection();
				ps = conn.prepareStatement(selectString);
				ps.setInt(1, movieId);
				resultSet = ps.executeQuery();
				resultSet.next();
				movie = new Movie();
				movie.setMovieId(movieId);
				movie.setMovieName(resultSet.getString(2));
				movie.setMovieBanner(resultSet.getString(3));
				movie.setReleaseDate(resultSet.getDate(4));
				movie.setRentAmount(resultSet.getInt(5));
				movie.setMovieCategory(resultSet.getString(6));
				movie.setAvailableCopy(resultSet.getInt(7));
			} finally {
				DaoHelper.close(ps);
				DaoHelper.close(resultSet);
				DaoHelper.close(conn);
			}
//		}
		return movie;
	}
	
	public Person readPerson(int personId) throws SQLException {
//		Movie movie = null;//(Movie) cache.get(movieId);
//		if (movie == null) {
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet resultSet = null;
			String selectString = "select * from tblmember where memberID = ?";
			try {
				conn = getConnection();
				ps = conn.prepareStatement(selectString);
				ps.setInt(1, personId);
				resultSet = ps.executeQuery();
				resultSet.next();
				Person member = new Person();
				member.setMemberId(resultSet.getInt(1));
				member.setType(resultSet.getInt(2));
				member.setFirstName(resultSet.getString(3));
				member.setLastName(resultSet.getString(4));
				member.setAddress(resultSet.getString(5));
				member.setCity(resultSet.getString(6));
				member.setState(resultSet.getString(7));
				member.setPhoneNo(resultSet.getString(8));
				member.setOutstandingNo(resultSet.getInt(9));
				member.setBalance(resultSet.getInt(10));
				member.setEmailId(resultSet.getString(12));
				member.setZipCode(resultSet.getString(13));
				return member;
			} finally {
				DaoHelper.close(ps);
				DaoHelper.close(resultSet);
				DaoHelper.close(conn);
			}
//		}
		
	}

	public List<Movie> getMoviesByCategory(String category) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		List<Movie> list = new ArrayList<Movie>();
		String selectString = "select * from tblmovie where category = ?";
		try {
			conn = getConnection();
			ps = conn.prepareStatement(selectString);
			ps = conn.prepareStatement(selectString);
			ps.setString(1, category);
			resultSet = ps.executeQuery();
			while (resultSet.next()) {
				resultSet.next();
				Movie movie = new Movie();
				movie.setMovieId(resultSet.getInt(1));
				movie.setMovieName(resultSet.getString(2));
				movie.setMovieBanner(resultSet.getString(3));
				movie.setReleaseDate(resultSet.getDate(4));
				movie.setRentAmount(resultSet.getInt(5));
				movie.setMovieCategory(resultSet.getString(6));
				movie.setAvailableCopy(resultSet.getInt(7));
				list.add(movie);
			}
			return list;
		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(resultSet);
			DaoHelper.close(conn);
		}
	}

	public List<Movie> getAllMovies() throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		List<Movie> list = new ArrayList<Movie>();
		String selectString = "select * from tblmovie";
		try {
			conn = getConnection();
			ps = conn.prepareStatement(selectString);
			resultSet = ps.executeQuery();
			while (resultSet.next()) {
				Movie movie = new Movie();
				movie.setMovieId(resultSet.getInt(1));
				movie.setMovieName(resultSet.getString(2));
				movie.setMovieBanner(resultSet.getString(3));
				movie.setReleaseDate(resultSet.getDate(4));
				movie.setRentAmount(resultSet.getInt(5));
				movie.setMovieCategory(resultSet.getString(6));
				movie.setTotalCopies(resultSet.getInt(7));
				movie.setAvailableCopy(resultSet.getInt(8));
				list.add(movie);
			}
			return list;
		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(resultSet);
			DaoHelper.close(conn);
		}
	}

	public int deleteMovie(int movieId) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		String updateString = "delete from tblmovie where movieID = ?";
		try {
			conn = getConnection();
			ps = conn.prepareStatement(updateString);
			ps.setInt(1, movieId);
			return ps.executeUpdate();
		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(conn);
		}
	}
	
	

	public int issueMovie(Movie movie, Person person) throws SQLException {
		Connection conn = null;
		PreparedStatement psQuery = null;
		PreparedStatement psQuery2 = null;
		PreparedStatement psQuery3 = null;
		PreparedStatement psInsert = null;
		PreparedStatement psUpdateAvailCopies = null;
		PreparedStatement psUpdateBalance = null;
		ResultSet resultSet = null;
		ResultSet resultSet2 = null;
		ResultSet resultSet3 = null;

		String selectString = "select count(1) as outstanding, type from tblrenttransaction inner join tblmember on "
				+ "tblmember.memberID = tblrenttransaction.memberID where tblmember.memberID = ? and tblrenttransaction.outstanding = true";
		String selectString2 = "select count(1) as alreadyissued from tblrenttransaction where memberID = ? and movieID = ? and outstanding = 1";
		String insertString = "insert into tblrenttransaction (movieID, memberID, rentDate, dueDate, outstanding)"
				+ "values(?, ?, ?, ?, ?)";
		String updateString = "update tblmember set balance = balance + ? where memberID = ?";
		
		
		// Set rent date and duedate
		Calendar cal = Calendar.getInstance();
		Date date = new Date(cal.getTimeInMillis());
		cal.add(Calendar.DATE, 10); // rent time is always 10 days...
		Date dueDate = new Date(cal.getTimeInMillis());
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			//Is member allowed to rent more
			psQuery = conn.prepareStatement(selectString);
			psQuery.setInt(1, person.getMemberId());
			resultSet = psQuery.executeQuery();
			resultSet.next();
			int outstandingNo = resultSet.getInt("outstanding");
			int type = resultSet.getInt("type");
			if (type == 1) {
				if (outstandingNo >= DaoConst.SIMPLE_LIMIT)
					return DaoConst.EXCEEDED_LIMIT;
			} else if (type == 2) {
				if (outstandingNo >= DaoConst.PREMIUM_LIMIT)
					return DaoConst.EXCEEDED_LIMIT;
			}
			
			//Has member already rented movie
			psQuery3 = conn.prepareStatement(selectString2);
			psQuery3.setInt(1, person.getMemberId());
			psQuery3.setInt(2, movie.getMovieId());
			resultSet3 = psQuery3.executeQuery();
			resultSet3.next();
			int alreadyIssued = resultSet3.getInt("alreadyissued");
			if (alreadyIssued > 0) {
				return DaoConst.ALREADY_ISSUED;
			}

			// Find out if copies of the movie is available and update available
			// copies field
			psQuery2 = conn.prepareStatement("select availcopies, rentamount from tblmovie where movieID = ?");
			psQuery2.setInt(1, movie.getMovieId());
			resultSet2 = psQuery2.executeQuery();
			resultSet2.next();
			int availCopies = resultSet2.getInt("availcopies");
			int rentAmount = resultSet2.getInt("rentamount");
			if (availCopies < 1) {
				return DaoConst.MOVIE_UNAVAILABLE;
			} else {
				psUpdateAvailCopies = conn.prepareStatement("update tblmovie set availcopies = availcopies-1 where movieID = ?");
				psUpdateAvailCopies.setInt(1, movie.getMovieId());
				psUpdateAvailCopies.executeUpdate();
				if(type == 1) {
					psUpdateBalance = conn.prepareStatement(updateString);
					psUpdateBalance.setInt(1, rentAmount);
					psUpdateBalance.setInt(2, person.getMemberId());
					psUpdateBalance.executeUpdate();
				}
			}

			// Insert rentaltransaction
			psInsert = conn.prepareStatement(insertString);
			psInsert.setInt(1, movie.getMovieId());
			psInsert.setInt(2, person.getMemberId());
			psInsert.setDate(3, new java.sql.Date(date.getTime()));
			psInsert.setDate(4, new java.sql.Date(dueDate.getTime()));
			psInsert.setBoolean(5, true);
			int resultStatus = psInsert.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
		} finally {
			DaoHelper.close(psQuery);
			DaoHelper.close(psQuery2);
			DaoHelper.close(psInsert);
			DaoHelper.close(psUpdateAvailCopies);
			DaoHelper.close(psUpdateBalance);
			DaoHelper.close(resultSet);
			DaoHelper.close(resultSet2);
			DaoHelper.close(resultSet3);
			DaoHelper.close(conn);
		}
		return 0;
	}

	public List<Person> getAllPremiumMembers() throws SQLException {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		List<Person> list = new ArrayList<Person>();
		String selectString = "select * from tblmember where type = 2";
		try {
			conn = getConnection();
			ps = conn.prepareStatement(selectString);
			resultSet = ps.executeQuery();
			while (resultSet.next()) {
				Person member = new Person();
				member.setMemberId(resultSet.getInt(1));
				member.setType(resultSet.getInt(2));
				member.setFirstName(resultSet.getString(3));
				member.setLastName(resultSet.getString(4));
				member.setAddress(resultSet.getString(5));
				member.setCity(resultSet.getString(6));
				member.setState(resultSet.getString(7));
				member.setPhoneNo(resultSet.getString(8));
				member.setOutstandingNo(resultSet.getInt(9));
				member.setBalance(resultSet.getInt(10));
				member.setEmailId(resultSet.getString(12));
				member.setZipCode(resultSet.getString(13));
				list.add(member);
			}
			return list;
		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(resultSet);
			DaoHelper.close(conn);
		}
	}

	public List<Person> getAllSimpleCustomers() throws SQLException {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		List<Person> list = new ArrayList<Person>();
		String selectString = "select * from tblmember where type = 1";
		try {
			conn = getConnection();
			ps = conn.prepareStatement(selectString);
			resultSet = ps.executeQuery();
			while (resultSet.next()) {
				Person member = new Person();
				member.setMemberId(resultSet.getInt(1));
				member.setType(resultSet.getInt(2));
				member.setFirstName(resultSet.getString(3));
				member.setLastName(resultSet.getString(4));
				member.setAddress(resultSet.getString(5));
				member.setCity(resultSet.getString(6));
				member.setState(resultSet.getString(7));
				member.setPhoneNo(resultSet.getString(8));
				member.setOutstandingNo(resultSet.getInt(9));
				member.setBalance(resultSet.getInt(10));
				member.setEmailId(resultSet.getString(12));
				member.setZipCode(resultSet.getString(13));
				list.add(member);
			}
			return list;
		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(resultSet);
			DaoHelper.close(conn);
		}
	}

	public List<Person> getAllPersons() throws SQLException {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		List<Person> list = new ArrayList<Person>();
		String selectString = "select * from tblmember";
		try {
			conn = getConnection();
			ps = conn.prepareStatement(selectString);
			resultSet = ps.executeQuery();
			while (resultSet.next()) {
				Person member = new Person();
				member.setMemberId(resultSet.getInt(1));
				member.setType(resultSet.getInt(2));
				member.setFirstName(resultSet.getString(3));
				member.setLastName(resultSet.getString(4));
				member.setAddress(resultSet.getString(5));
				member.setCity(resultSet.getString(6));
				member.setState(resultSet.getString(7));
				member.setPhoneNo(resultSet.getString(8));
				member.setOutstandingNo(resultSet.getInt(9));
				member.setBalance(resultSet.getInt(10));
				member.setEmailId(resultSet.getString(12));
				member.setZipCode(resultSet.getString(13));
				list.add(member);
			}
			return list;
		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(resultSet);
			DaoHelper.close(conn);
		}
	}
	
	public int createPerson(Person member) throws SQLException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		int result;
		
		try {
			String selectString = "select memberID from tblmember where emailid = ?";
			
			String insertString = "insert into" +
			" tblmember(type,fname,lname,address,city,state,phoneNo,outMov,balance,emailid,zipcode)" +
			" values ("+member.getType()+",'"+member.getFirstName()+
			"','"+member.getLastName()+"','"+member.getAddress()+"','"+member.getCity()+
			"','"+member.getState()+"',"+member.getphoneNo()+","+member.getOutstandingNo()+","+member.getBalance()+
			",'"+member.getEmailId()+"','"+member.getZipCode()+"')";

			conn = getConnection();
			ps = conn.prepareStatement(selectString);			
			ps.setString(1, member.getEmailId());
			resultSet = ps.executeQuery();
			//If person exists already
			if(resultSet.next()) {
				result = DaoConst.DUPLICATE;
			}
			else {
				ps = conn.prepareStatement(insertString);
				result = ps.executeUpdate();														
			}
			return result;
		}
		catch (Exception e) {
			e.printStackTrace();
			return -1;
		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(resultSet);
			DaoHelper.close(conn);
		}
	}

	public Boolean deletePerson(long memberid) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;

		try {

			String selectString = "delete from tblmember where memberID = "
					+ memberid;

			conn = getConnection2();
			ps = conn.prepareStatement(selectString);
			int resultStatus = ps.executeUpdate();
			if (resultStatus == 1)
				return true;
			else
				return false;
		} catch (Exception e) {
			return false;
		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(resultSet);
			DaoHelper.close(conn);
		}
	}

	public Boolean updatePerson(Person member) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;

		try {
			int memberId = member.getMemberId();

			String selectString = "select * from tblmember where memberID ="
					+ memberId;
			conn = getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(selectString,
					ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			resultSet = ps.executeQuery();
			resultSet.next();
			
			if(member.getType()!= 0)
				resultSet.updateInt(2,member.getType() );
			if(member.getFirstName()!= null)
				resultSet.updateString(3,member.getFirstName() );
			if(member.getLastName()!= null)
				resultSet.updateString(4,member.getLastName() );
			if(member.getAddress()!= null)
				resultSet.updateString(5,member.getAddress() );
			if(member.getCity()!= null)
				resultSet.updateString(6,member.getCity() );
			if(member.getState()!= null)
				resultSet.updateString(7,member.getState() );
			if(member.getphoneNo()!= null)
				resultSet.updateString(8,member.getphoneNo() );
			if(member.getOutstandingNo()!= 0)
				resultSet.updateInt(9,member.getOutstandingNo() );
			if(member.getEmailId()!= null)
				resultSet.updateString(12,member.getEmailId() );
			if(member.getZipCode()!= null)
				resultSet.updateString(13,member.getZipCode() );
			resultSet.updateRow();
			conn.commit();
			return true;

		} catch (Exception e) {
			return false;
		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(resultSet);
			DaoHelper.close(conn);
		}
	}

	public void updateMovie(Movie movie) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		String sqlString = "select * from tblmovie where movieID = ?";
		System.out.println("-" + movie.getMovieCategory());
		try {
			conn = getConnection2();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sqlString, ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_UPDATABLE);
			ps.setInt(1, movie.getMovieId());
			resultSet = ps.executeQuery();
			resultSet.next();
			if (movie.getMovieName() != null)
				resultSet.updateString("moviename", movie.getMovieName());
			if (movie.getMovieBanner() != null)
				resultSet.updateString("mbanner", movie.getMovieBanner());
			if (movie.getReleaseDate() != null)
				resultSet.updateDate("releasedate", new java.sql.Date(movie.getReleaseDate().getTime()));
			if (movie.getRentAmount() != -1)
				resultSet.updateInt("rentamount", movie.getRentAmount());
			if (movie.getMovieCategory() != null)
				resultSet.updateString("category", movie.getMovieCategory());
			if (movie.getAvailableCopy() != -1)
				resultSet.updateInt("availcopies", movie.getAvailableCopy());
			if (movie.getTotalCopies() != -1)
				resultSet.updateInt("totalcopies", movie.getTotalCopies());
			System.out.println("---" + movie.getMovieCategory());
			resultSet.updateRow();
			
			conn.commit();

			//cache.remove(movie.getMovieId());

		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(resultSet);
			DaoHelper.close(conn);
		}
	}

	public List<Movie> getAllMoviesByPerson(int memberID) throws SQLException {		

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		List<Movie> list = new ArrayList<Movie>();

		String selectString = "select * from tblrenttransaction inner join tblmovie " +
				"on tblrenttransaction.movieID = tblmovie.movieID where tblrenttransaction.memberID = ? Order by memberID desc";

		try {
			conn = getConnection();
			ps = conn.prepareStatement(selectString);
			ps.setInt(1, memberID);
			resultSet = ps.executeQuery();
			while (resultSet.next()) {
				Movie movie = new Movie();
				movie.setMovieId(resultSet.getInt("movieID"));
				movie.setMovieName(resultSet.getString("moviename"));
				movie.setMovieBanner(resultSet.getString("mbanner"));
				movie.setReleaseDate(resultSet.getDate("dueDate"));
				movie.setRentAmount(resultSet.getInt("rentamount"));
				movie.setTotalCopies(resultSet.getInt("outstanding"));
				movie.setAvailableCopy(resultSet.getInt("availcopies"));
				list.add(movie);
			}
			return list;
		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(resultSet);
			DaoHelper.close(conn);
		}
	}

	public List<Person> findPerson(String firstName, String lastName, String address, String city, String state, String zipcode, String emailaddress, String phoneno) throws SQLException {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		List<Person> list = new ArrayList<Person>();
		Boolean bOr = false;
		String selectString = "select * from tblmember where ";
		
		if(!firstName.equals(""))
		{
			selectString += " fname like '%" + firstName + "%'";
			bOr = true;
		}
		
		if(!lastName.equals(""))
		{
			if (bOr)
				selectString += " or ";
			selectString += " lname like '%" + lastName + "%'";
			bOr = true;
		}
		
		if(!address.equals(""))
		{
			if (bOr)
				selectString += " or ";
			selectString += " address like '%" + address + "%'";
			bOr = true;
		}
		
		if(!city.equals(""))
		{
			if (bOr)
				selectString += " or ";
			selectString += " city like '%" + city + "%'";
			bOr = true;
		}
		
		if(!state.equals(""))
		{
			if (bOr)
				selectString += " or ";
			selectString += " state like '%" + state + "%'";
			bOr = true;
		}
							 							
		if(!phoneno.equals(""))
		{
			if (bOr)
				selectString += " or ";
			selectString += " phoneNo like '%" + phoneno + "%'";
			bOr = true;
		}					
							
		if(!emailaddress.equals(""))
		{
			if (bOr)
				selectString += " or ";
			selectString += " emailid like '%" + emailaddress + "%'";
			bOr = true;
		}						
		
		if(!zipcode.equals(""))
		{
			if (bOr)
				selectString += " or ";
			selectString += " zipcode like '%" + zipcode + "%'";
			bOr = true;
		}																											
							
							
		try {
			conn = getConnection2();
			ps = conn.prepareStatement(selectString);
			resultSet = ps.executeQuery();								
			while(resultSet.next()){
				//resultSet.next();
				Person member = new Person();
				member.setMemberId(resultSet.getInt(1));
				member.setType(resultSet.getInt(2));
				member.setFirstName(resultSet.getString(3));
				member.setLastName(resultSet.getString(4));
				member.setAddress(resultSet.getString(5));
				member.setCity(resultSet.getString(6));
				member.setState(resultSet.getString(7));
				member.setPhoneNo(resultSet.getString(8));
				member.setOutstandingNo(resultSet.getInt(9));
				member.setBalance(resultSet.getInt(10));
				member.setDueDate(resultSet.getDate(11));
				member.setEmailId(resultSet.getString(12));
				member.setZipCode(resultSet.getString(13));
				list.add(member);
			}
			return list;
		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(resultSet);
			DaoHelper.close(conn);
		}				
	}

	public List<Movie> findMovie(Movie movie) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		List<Movie> list = new ArrayList<Movie>();
		
		
		Boolean bOr = true;
		String selectString = "select * from tblmovie where";
		System.out.println(selectString);
		if(movie.getMovieName() != null && !movie.getMovieName().equals(""))
		{
			selectString += " moviename like '%" + movie.getMovieName() + "%'";
		}
		System.out.println(selectString);
		if(movie.getMovieBanner() != null && !movie.getMovieBanner().equals(""))
		{
			if (bOr && !selectString.equals("select * from tblmovie where"))
				selectString += " and ";
			selectString += " mbanner like '%" + movie.getMovieBanner() + "%'";
		}
		System.out.println(selectString);
		if(movie.getMovieCategory() != null && !movie.getMovieCategory().equals(""))
		{
			if (bOr && !selectString.equals("select * from tblmovie where"))
				selectString += " and ";
			selectString += " category like '%" + movie.getMovieCategory() + "%'";
		}
		
		System.out.println(selectString);
		if(!(movie.getReleaseDate() == null))
		{
			DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
			String year = df.format(movie.getReleaseDate()).substring(0, 4);
			if (bOr && !selectString.equals("select * from tblmovie where"))
				selectString += " and ";
			selectString += " releasedate between '"+year+"-01-01' and '"+year+"-12-31'";
		}					
			System.out.println(selectString);				
		try {
			conn = getConnection();
			ps = conn.prepareStatement(selectString);
			resultSet = ps.executeQuery();								
			while(resultSet.next()){

				Movie movie2 = new Movie();
				movie2.setMovieId(resultSet.getInt("movieID"));
				movie2.setMovieName(resultSet.getString(2));
				System.out.println(movie2.getMovieName());
				movie2.setMovieBanner(resultSet.getString(3));
				movie2.setReleaseDate(resultSet.getDate(4));
				movie2.setRentAmount(resultSet.getInt(5));
				movie2.setMovieCategory(resultSet.getString(6));
				movie2.setAvailableCopy(resultSet.getInt(7));
				list.add(movie2);

			}
			return list;
		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(resultSet);
			DaoHelper.close(conn);
		}				
	}
	
	public List<RentalTransaction> getHistory(int memberId) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		List<RentalTransaction> list = new ArrayList<RentalTransaction>();

		String selectString = "select * from tblrenttransaction where memberID = "
				+ memberId;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(selectString);
			resultSet = ps.executeQuery();
			while (resultSet.next()) {
				RentalTransaction rent = new RentalTransaction();
				rent.setRentalid(resultSet.getInt(1));
				rent.setMovieId(resultSet.getInt(2));
				rent.setMemberId(resultSet.getInt(3));
				rent.setRentDate(resultSet.getDate(4));
				rent.setDueDate(resultSet.getDate(5));
				rent.setOutstanding(resultSet.getInt(6));
				list.add(rent);
			}
			return list;
		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(resultSet);
			DaoHelper.close(conn);
		}
	}

	public List<Person> getAllPersonsByMovie(int movieId) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		List<Person> list = new ArrayList<Person>();
		String selectString = "select * from tblrenttransaction inner join tblmember "
				+ "on tblrenttransaction.movieID = tblmember.movieID where tblrenttransaction.movieID = ?";
		try {
			conn = getConnection();
			ps = conn.prepareStatement(selectString);
			ps.setInt(1, movieId);
			resultSet = ps.executeQuery();
			while (resultSet.next()) {
				Person person = new Person();
				person.setMemberId(resultSet.getInt("memberID"));
				person.setFirstName(resultSet.getString("fname"));
				person.setLastName(resultSet.getString("lname"));
				list.add(person);
			}
			return list;
		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(resultSet);
			DaoHelper.close(conn);
		}
	}

	public boolean login(String username, String password) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		System.out.println(username + " " + password);
		String selectString = "select count(1) as login from tbladminuser where email = ? and password = ?";
		try {
			conn = getConnection();
			ps = conn.prepareStatement(selectString);
			ps.setString(1, username);
			ps.setString(2, password);
			resultSet = ps.executeQuery();
			if(resultSet.next()) {
				if(resultSet.getInt("login") > 0) return true;
			}
			return false;
		} finally {
			DaoHelper.close(ps);
			DaoHelper.close(resultSet);
			DaoHelper.close(conn);
		}
	}
}